﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Library.Entity;
using Library.Connection;
using System.Data.SqlClient;
using Library.SQL;
using System.Collections;
using System.Data;
using MySql.Data.MySqlClient;

namespace Library.Services
{
    public class BookTransactionService
    {
        DataBaseConnectivity m_dataBaseConectivity = new DataBaseConnectivity();
        BookStockService m_BookStockService = new BookStockService(); 
        public BookTransactionService()
        { }
        internal BookTransaction GetBookTransactionById(int Id)
        {
            BookTransaction result = null;
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectBookTransactionById(), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("Id", MySqlDbType.Int32).Value = Id;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new BookTransaction(rd);
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }
        internal BookTransaction GetBookTransactionByBookId(int BookId)
        {
            BookTransaction result = null;
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectBookTransactionByBookId(), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("BookId", MySqlDbType.Int32).Value = BookId;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new BookTransaction(rd);
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }
        internal BookTransaction GetBookTransactionByBookTransactionCode(string BookTransactionCode)
        {
            BookTransaction result = null;
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectBookTransactionByCode(), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("Code", MySqlDbType.VarChar).Value = BookTransactionCode;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new BookTransaction(rd);
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }
        internal IList GetBookTransactionByBookTransactionCodeList(string BookTransactionCode)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectBookTransactionByCode(), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("Code", MySqlDbType.VarChar).Value = BookTransactionCode;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new BookTransaction(rd));
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }
        internal void InsertLog(BookTransaction m_BookTransaction, string p)
        {
          
        }
        internal IList GetAllBookTransaction()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectAllBookTransaction(), m_dataBaseConectivity.MySqlConnection);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new BookTransaction(rd));
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }
        internal void InsertNewBookTransaction(BookTransaction BookTransaction)
        {
            try
            {
                BookStock bs = m_BookStockService.GetBookStockByBookId(BookTransaction.BookId);
                if (BookTransaction.TransactionType == TransactionType.Borrow || BookTransaction.TransactionType == TransactionType.Lost || BookTransaction.TransactionType == TransactionType.Broken)
                {
                    if (bs.BookBalance == 0)
                        throw new Exception("There is no book stock available for this transaction, book stock is zero. Please add stock for book if book is available.");
                    if (bs.BookBalance < BookTransaction.Quantity)
                        throw new Exception("Book Stock is only : " + bs.BookBalance + ", There is no book stock available for this transaction. Please add stock for book if book is available.");
                }

                BookTransaction.Code = GenerateCode(BookTransaction.TransactionType);

                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.InsertBookTransaction(), m_dataBaseConectivity.MySqlConnection);

                cmd.Parameters.Add("Code", MySqlDbType.VarChar).Value = BookTransaction.Code;
                cmd.Parameters.Add("Name", MySqlDbType.VarChar).Value = BookTransaction.Name;
                cmd.Parameters.Add("Remark", MySqlDbType.VarChar).Value = BookTransaction.Remark;
                cmd.Parameters.Add("CreatedDate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("ModifiedDate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("CreatedUserId", MySqlDbType.Int32).Value = BookTransaction.CreatedUserId;
                cmd.Parameters.Add("ModifiedUserId", MySqlDbType.Int32).Value = BookTransaction.ModifiedUserId;

                cmd.Parameters.Add("BookId", MySqlDbType.Int32).Value = BookTransaction.BookId;
                cmd.Parameters.Add("BookStockId", MySqlDbType.Int32).Value = bs.Id;
                cmd.Parameters.Add("BorrowId", MySqlDbType.Int32).Value = BookTransaction.BorrowId;
                cmd.Parameters.Add("MemberId", MySqlDbType.Int32).Value = BookTransaction.MemberId;
                cmd.Parameters.Add("TransactionDate", MySqlDbType.Datetime).Value = BookTransaction.TransactionDate;
                cmd.Parameters.Add("BorrowDate", MySqlDbType.Datetime).Value = BookTransaction.BorrowDate;
                cmd.Parameters.Add("ReturnDate", MySqlDbType.Datetime).Value = BookTransaction.ReturnDate;
                cmd.Parameters.Add("TransactionType", MySqlDbType.Int16).Value = BookTransaction.TransactionType;
                cmd.Parameters.Add("Quantity", MySqlDbType.Decimal).Value = BookTransaction.Quantity;
                
                cmd.ExecuteNonQuery();
                cmd.CommandText = SQLScript.SelectMaxBookTransactionId();
                cmd.Parameters.Clear();
                BookTransaction.Id = Convert.ToInt32(cmd.ExecuteScalar());

                switch (BookTransaction.TransactionType)
                {
                    case TransactionType.Borrow :
                        bs.BookBalance = bs.BookBalance - BookTransaction.Quantity;
                        break;
                    case TransactionType.Donation:
                        bs.BookBalance = bs.BookBalance + BookTransaction.Quantity;
                        break;
                    case TransactionType.Lost:
                        bs.BookBalance = bs.BookBalance - BookTransaction.Quantity;
                        break;
                    case TransactionType.Broken:
                        bs.BookBalance = bs.BookBalance - BookTransaction.Quantity;
                        break;
                    case TransactionType.Purchase:
                        bs.BookBalance = bs.BookBalance + BookTransaction.Quantity;
                        break;
                    case TransactionType.Return:
                        bs.BookBalance = bs.BookBalance + BookTransaction.Quantity;
                        break;
                }
                m_BookStockService.UpdateBookStock(bs);
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
        }
        internal void UpdateBookTransaction(BookTransaction BookTransaction)
        {
            try
            {
                BookStock bs = m_BookStockService.GetBookStockByBookId(BookTransaction.BookId);
                BookTransaction originalTr = GetBookTransactionById(BookTransaction.Id);
                if (BookTransaction.TransactionType == TransactionType.Borrow || BookTransaction.TransactionType == TransactionType.Lost || BookTransaction.TransactionType == TransactionType.Broken)
                {
                    bs.BookBalance = bs.BookBalance + originalTr.Quantity;
                }
                else
                {
                    if (bs.BookBalance == 0)
                        throw new Exception("There is no book stock available for this transaction, book stock is zero. Please add stock for book if book is available.");
                    if (bs.BookBalance < originalTr.Quantity)
                        throw new Exception("Book Stock is only : " + bs.BookBalance + ", There is no book stock available for this transaction. Please add stock for book if book is available.");
                    bs.BookBalance = bs.BookBalance - originalTr.Quantity;
                }

                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.UpdateBookTransaction(), m_dataBaseConectivity.MySqlConnection);

                cmd.Parameters.Add("Code", MySqlDbType.VarChar).Value = BookTransaction.Code;
                cmd.Parameters.Add("Name", MySqlDbType.VarChar).Value = BookTransaction.Name;
                cmd.Parameters.Add("Remark", MySqlDbType.VarChar).Value = BookTransaction.Remark;
                cmd.Parameters.Add("ModifiedDate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("ModifiedUserId", MySqlDbType.Int32).Value = BookTransaction.ModifiedUserId;

                cmd.Parameters.Add("BookId", MySqlDbType.Int32).Value = BookTransaction.BookId;
                cmd.Parameters.Add("BookStockId", MySqlDbType.Int32).Value = BookTransaction.BookStockId;
                cmd.Parameters.Add("BorrowId", MySqlDbType.Int32).Value = BookTransaction.BorrowId;
                cmd.Parameters.Add("MemberId", MySqlDbType.Int32).Value = BookTransaction.MemberId;
                cmd.Parameters.Add("TransactionDate", MySqlDbType.Datetime).Value = BookTransaction.TransactionDate;
                cmd.Parameters.Add("BorrowDate", MySqlDbType.Datetime).Value = BookTransaction.BorrowDate;
                cmd.Parameters.Add("ReturnDate", MySqlDbType.Datetime).Value = BookTransaction.ReturnDate;
                cmd.Parameters.Add("TransactionType", MySqlDbType.Int16).Value = BookTransaction.TransactionType;
                cmd.Parameters.Add("Quantity", MySqlDbType.Decimal).Value = BookTransaction.Quantity;

                cmd.Parameters.Add("Id", MySqlDbType.Int32).Value = BookTransaction.Id;
                cmd.ExecuteNonQuery();

                switch (BookTransaction.TransactionType)
                {
                    case TransactionType.Borrow:
                        bs.BookBalance = bs.BookBalance - BookTransaction.Quantity;
                        break;
                    case TransactionType.Donation:
                        bs.BookBalance = bs.BookBalance + BookTransaction.Quantity;
                        break;
                    case TransactionType.Lost:
                        bs.BookBalance = bs.BookBalance - BookTransaction.Quantity;
                        break;
                    case TransactionType.Broken:
                        bs.BookBalance = bs.BookBalance - BookTransaction.Quantity;
                        break;
                    case TransactionType.Purchase:
                        bs.BookBalance = bs.BookBalance + BookTransaction.Quantity;
                        break;
                    case TransactionType.Return:
                        bs.BookBalance = bs.BookBalance + BookTransaction.Quantity;
                        break;
                }
                m_BookStockService.UpdateBookStock(bs);
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
        }
        internal void DeleteBookTransaction(BookTransaction BookTransaction)
        {
            try
            {
                BookStock bs = m_BookStockService.GetBookStockByBookId(BookTransaction.BookId);
                if (BookTransaction.TransactionType == TransactionType.Borrow || BookTransaction.TransactionType == TransactionType.Lost || BookTransaction.TransactionType == TransactionType.Broken)
                {
                    bs.BookBalance = bs.BookBalance + BookTransaction.Quantity;
                }
                else
                {
                    if (bs.BookBalance == 0)
                        throw new Exception("There is no book stock available for this transaction, book stock is zero. Please add stock for book if book is available.");
                    if (bs.BookBalance < BookTransaction.Quantity)
                        throw new Exception("Book Stock is only : " + bs.BookBalance + ", There is no book stock available for this transaction. Please add stock for book if book is available.");
                    bs.BookBalance = bs.BookBalance - BookTransaction.Quantity;
                }
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.DeleteBookTransaction(), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("Id", MySqlDbType.Int32).Value = BookTransaction.Id;
                cmd.ExecuteNonQuery();
                m_BookStockService.UpdateBookStock(bs);
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
        }

        internal void InsertNewBookTransactionBorrow(BookTransaction BookTransaction)
        {
            try
            {
                foreach (BookTransaction trbook in BookTransaction.BorrowList)
                {
                    BookStock bs = m_BookStockService.GetBookStockByBookId(trbook.BookId);
                    if (bs.BookBalance == 0)
                        throw new Exception("There is no book stock available for this transaction, book stock is zero. Please add stock for book if book is available.");
                    if (bs.BookBalance < BookTransaction.Quantity)
                        throw new Exception("Book Stock is only : " + bs.BookBalance + ", There is no book stock available for this transaction. Please add stock for book if book is available.");
                }

                BookTransaction.Code = GenerateCode(BookTransaction.TransactionType);
                m_dataBaseConectivity.MySqlConnection_b.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.InsertBookTransaction(), m_dataBaseConectivity.MySqlConnection_b);

                foreach (BookTransaction trbook in BookTransaction.BorrowList)
                {
                    BookStock bs = m_BookStockService.GetBookStockByBookId(trbook.BookId);
                    cmd.CommandText = SQLScript.InsertBookTransaction();
                    cmd.Parameters.Add("Code", MySqlDbType.VarChar).Value = BookTransaction.Code;
                    cmd.Parameters.Add("Name", MySqlDbType.VarChar).Value = trbook.Book.ToString();
                    cmd.Parameters.Add("Remark", MySqlDbType.VarChar).Value = BookTransaction.Remark;
                    cmd.Parameters.Add("CreatedDate", MySqlDbType.Datetime).Value = DateTime.Now;
                    cmd.Parameters.Add("ModifiedDate", MySqlDbType.Datetime).Value = DateTime.Now;
                    cmd.Parameters.Add("CreatedUserId", MySqlDbType.Int32).Value = BookTransaction.CreatedUserId;
                    cmd.Parameters.Add("ModifiedUserId", MySqlDbType.Int32).Value = BookTransaction.ModifiedUserId;

                    cmd.Parameters.Add("BookId", MySqlDbType.Int32).Value = trbook.BookId;
                    cmd.Parameters.Add("BookStockId", MySqlDbType.Int32).Value = bs.Id;
                    cmd.Parameters.Add("BorrowId", MySqlDbType.Int32).Value = BookTransaction.BorrowId;
                    cmd.Parameters.Add("MemberId", MySqlDbType.Int32).Value = BookTransaction.MemberId;
                    cmd.Parameters.Add("TransactionDate", MySqlDbType.Datetime).Value = BookTransaction.TransactionDate;
                    cmd.Parameters.Add("BorrowDate", MySqlDbType.Datetime).Value = BookTransaction.BorrowDate;
                    cmd.Parameters.Add("ReturnDate", MySqlDbType.Datetime).Value = BookTransaction.ReturnDate;
                    cmd.Parameters.Add("TransactionType", MySqlDbType.Int16).Value = BookTransaction.TransactionType;
                    cmd.Parameters.Add("Quantity", MySqlDbType.Decimal).Value = trbook.Quantity;

                    cmd.ExecuteNonQuery();
                    cmd.CommandText = SQLScript.SelectMaxBookTransactionId();
                    cmd.Parameters.Clear();
                    BookTransaction.Id = Convert.ToInt32(cmd.ExecuteScalar());

                    bs.BookBalance = bs.BookBalance - (Convert.ToDecimal(trbook.Quantity));
                    m_BookStockService.UpdateBookStock(bs);
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection_b.Close();
            }
        }

        internal void UpdateBookTransactionBorrow(BookTransaction BookTransaction)
        {
            try
            {
                IList originalTr = GetBookTransactionByBookTransactionCodeList(BookTransaction.Code);
                foreach (BookTransaction trbook in originalTr)
                    DeleteBookTransaction(trbook);

                m_dataBaseConectivity.MySqlConnection_b.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.InsertBookTransaction(), m_dataBaseConectivity.MySqlConnection_b);

                foreach (BookTransaction trbook in BookTransaction.BorrowList)
                {
                    BookStock bs = m_BookStockService.GetBookStockByBookId(trbook.BookId);
                    cmd.CommandText = SQLScript.InsertBookTransaction();
                    cmd.Parameters.Add("Code", MySqlDbType.VarChar).Value = BookTransaction.Code;
                    cmd.Parameters.Add("Name", MySqlDbType.VarChar).Value = trbook.Book.ToString();
                    cmd.Parameters.Add("Remark", MySqlDbType.VarChar).Value = BookTransaction.Remark;
                    cmd.Parameters.Add("CreatedDate", MySqlDbType.Datetime).Value = DateTime.Now;
                    cmd.Parameters.Add("ModifiedDate", MySqlDbType.Datetime).Value = DateTime.Now;
                    cmd.Parameters.Add("CreatedUserId", MySqlDbType.Int32).Value = BookTransaction.CreatedUserId;
                    cmd.Parameters.Add("ModifiedUserId", MySqlDbType.Int32).Value = BookTransaction.ModifiedUserId;

                    cmd.Parameters.Add("BookId", MySqlDbType.Int32).Value = trbook.BookId;
                    cmd.Parameters.Add("BookStockId", MySqlDbType.Int32).Value = bs.Id;
                    cmd.Parameters.Add("BorrowId", MySqlDbType.Int32).Value = BookTransaction.BorrowId;
                    cmd.Parameters.Add("MemberId", MySqlDbType.Int32).Value = BookTransaction.MemberId;
                    cmd.Parameters.Add("TransactionDate", MySqlDbType.Datetime).Value = BookTransaction.TransactionDate;
                    cmd.Parameters.Add("BorrowDate", MySqlDbType.Datetime).Value = BookTransaction.BorrowDate;
                    cmd.Parameters.Add("ReturnDate", MySqlDbType.Datetime).Value = BookTransaction.ReturnDate;
                    cmd.Parameters.Add("TransactionType", MySqlDbType.Int16).Value = BookTransaction.TransactionType;
                    cmd.Parameters.Add("Quantity", MySqlDbType.Decimal).Value = trbook.Quantity;

                    cmd.ExecuteNonQuery();
                    cmd.CommandText = SQLScript.SelectMaxBookTransactionId();
                    cmd.Parameters.Clear();
                    BookTransaction.Id = Convert.ToInt32(cmd.ExecuteScalar());

                    bs.BookBalance = bs.BookBalance - (Convert.ToDecimal(trbook.Quantity));
                    m_BookStockService.UpdateBookStock(bs);
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection_b.Close();
            }
        }

        internal void DeleteBookTransactionBorrow(BookTransaction BookTransaction)
        {
            try
            {
                foreach (BookTransaction trbook in BookTransaction.BorrowList)
                    DeleteBookTransaction(trbook);
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection_b.Close();
            }
        }

        internal IList GetAllBookTransactionByTransactionType(TransactionType transactionType, DateTime dateTime)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectAllBookTransactionByTransactionTypeAndDate(), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("TransactionDate", MySqlDbType.Datetime).Value = dateTime;
                cmd.Parameters.Add("TransactionType", MySqlDbType.Int16).Value = transactionType;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new BookTransaction(rd));
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }
        internal IList GetAllBookTransactionByTransactionType(TransactionType transactionType, DateTime dateTime,string code)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectAllBookTransactionByTransactionTypeAndDateAndBookCode(code), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("TransactionDate", MySqlDbType.Datetime).Value = dateTime;
                cmd.Parameters.Add("TransactionType", MySqlDbType.Int16).Value = transactionType;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new BookTransaction(rd));
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }
        internal IList GetAllBookTransactionByTransactionTypeMember(TransactionType transactionType, DateTime dateTime, string code)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectAllBookTransactionByTransactionTypeAndDateAndBookCodeMember(code), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("TransactionDate", MySqlDbType.Datetime).Value = dateTime;
                cmd.Parameters.Add("TransactionType", MySqlDbType.Int16).Value = transactionType;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new BookTransaction(rd));
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }
        internal IList GetAllBookTransactionByTransactionType(TransactionType transactionType, string code)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectAllBookTransactionByTransactionTypeAndBookCode(code), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("TransactionType", MySqlDbType.Int16).Value = (int)transactionType;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new BookTransaction(rd));
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }
        internal IList GetAllBookTransactionByTransactionTypeMember(TransactionType transactionType, string code)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectAllBookTransactionByTransactionTypeAndBookCodeMember(code), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("TransactionType", MySqlDbType.Int16).Value = (int)transactionType;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new BookTransaction(rd));
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }
        internal IList GetAllBookTransactionByTransactionType(TransactionType transactionType, int memberId)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectAllBookTransactionByTransactionTypeAndMember(), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("MemberId", MySqlDbType.Int32).Value = memberId;
                cmd.Parameters.Add("TransactionType", MySqlDbType.Int16).Value = transactionType;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new BookTransaction(rd));
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }
        internal int GetMaxBookTransactionId()
        {
            int result = 0;
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectMaxBookTransactionId(), m_dataBaseConectivity.MySqlConnection);
                if (cmd.ExecuteScalar() is DBNull)
                    return 0;
                result = Convert.ToInt32(cmd.ExecuteScalar());
                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
        }
        internal string GenerateCode(TransactionType type)
        {
            string prefix = type.ToString().Substring(0,3).ToUpper();// lastCOde.Substring(0, j);
            string result = "000001";
            try
            {
                int maxMemberId = GetMaxBookTransactionId();
                if(maxMemberId==0)
                    return prefix + result;
                BookTransaction latestUser = GetBookTransactionById(maxMemberId);
                if (latestUser == null)
                    return prefix + result;
                string lastCOde = latestUser.Code;

                int j = 0;
                for (int i = lastCOde.Length; i > -1; i--)
                {
                    string chr = lastCOde.Substring(i - 1, 1);
                    int p = 0;
                    if (!int.TryParse(chr, out p))
                    {
                        j = i;
                        break;
                    }
                }
                string codeInt = lastCOde.Substring(j, lastCOde.Length - j);
                int pad = codeInt.Length;
                int code = Convert.ToInt32(codeInt);
                int nextCode = code + 1;
                //string prefix = type.ToString();// lastCOde.Substring(0, j);
                result = prefix + nextCode.ToString().PadLeft(pad, '0');

                BookTransaction found = GetBookTransactionByBookTransactionCode(result);
                while (found != null)
                {
                    nextCode++;
                    result = prefix + nextCode.ToString().PadLeft(pad, '0');
                    found = GetBookTransactionByBookTransactionCode(result);
                }

                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
        }


        internal bool IsAlreadyReturn(BookTransaction bookBorrow)
        {
            int result = 0;
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectCountBorrowBook(), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("BorrowId", MySqlDbType.Int32).Value = bookBorrow.Id;
                cmd.Parameters.Add("TransactionType", MySqlDbType.Int16).Value =  TransactionType.Return;
                if (cmd.ExecuteScalar() is DBNull)
                    return false;
                result = Convert.ToInt32(cmd.ExecuteScalar());
                return result > 0;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
        }
    }
}